const { Sequelize, DataTypes } = require("sequelize");

// 从环境变量中读取数据库配置
const MYSQL_USERNAME = 'root';
const MYSQL_PASSWORD = '123456';

const host = 'localhost';
const port = '3306';
const sequelize = new Sequelize("xinli", MYSQL_USERNAME, MYSQL_PASSWORD, {
  host: host,
  port: port,
  dialect: "mysql",
  define: {
    timestamps: false // 全局禁用 timestamps
  }
});

// 用户模型
const User = sequelize.define("users", {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    username: {
        type: DataTypes.STRING(50),
        allowNull: false,
        unique: true
    },
    password: {
        type: DataTypes.STRING(100),
        allowNull: false
    },
    name: {
        type: DataTypes.STRING(50)
    },
    is_counselor: {
        type: DataTypes.BOOLEAN,
        defaultValue: false
    },
    is_assistantn: {
        type: DataTypes.BOOLEAN,
        defaultValue: false
    },
    is_admin: {
        type: DataTypes.BOOLEAN,
        defaultValue: false
    },
    id_status: {
        type: DataTypes.TINYINT,
        defaultValue: 0,
        comment: '账号审核状态：0 - 未审核 1 - 已审核 2 - 审核不通过'
    },
    phone: {
        type: DataTypes.STRING(50)
    },
    email: {
        type: DataTypes.STRING(50)
    },
    id_card: {
        type: DataTypes.STRING(20)
    },
    avatar: {
        type: DataTypes.STRING(500)
    },
    counselor_free_time :{
        type: DataTypes.JSON 
    },
    introduction: {
        type: DataTypes.STRING(200)
    }
}, {
    timestamps: false // 禁用默认的 createdAt 和 updatedAt 字段
});

// 心理知识模型
const PsychologyKnowledge = sequelize.define("psychology_knowledge", {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    title: {
        type: DataTypes.STRING(200),
        allowNull: false
    },
    content: {
        type: DataTypes.TEXT
    },
    likes: {
        type: DataTypes.INTEGER,
        defaultValue: 0
    },
    picture: {
        type: DataTypes.STRING(500)
    }
}, {
    timestamps: false,
    tableName: 'psychology_knowledge' // 明确指定表名
});

// 咨询室模型
const ConsultationRoom = sequelize.define("consultation_rooms", {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    reason: {
        type: DataTypes.STRING(255)
    },
    user_id: {
        type: DataTypes.INTEGER,
        references: {
            model: User,
            key: 'id'
        }
    },
    create_time: {
        type: DataTypes.DATE,
        defaultValue: DataTypes.NOW
    },
    appointment_date: {
        type: DataTypes.DATEONLY
    },
    duration: {
        type: DataTypes.INTEGER
    }
}, {
    timestamps: false
});

// 心理问卷模型
const PsychologyQuestionnaire = sequelize.define("psychology_questionnaires", {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    title: {
        type: DataTypes.STRING(100),
        allowNull: false
    },
    ps_introduction: {
        type: DataTypes.STRING(200)
    },
    scoring: {
        type: DataTypes.TEXT
    }
}, {
    timestamps: false
});

// 题目模型
const Question = sequelize.define("questions", {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    question_type: {
        type: DataTypes.STRING(20)
    },
    question_text: {
        type: DataTypes.TEXT
    }
}, {
    timestamps: false
});

// 题目选项模型
const QuestionOption = sequelize.define("question_options", {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    question_id: {
        type: DataTypes.INTEGER,
        references: {
            model: Question,
            key: 'id'
        }
    },
    option_text: {
        type: DataTypes.STRING(255)
    },
    option_score: {
        type: DataTypes.INTEGER
    }
}, {
    timestamps: false
});

// 问卷部分模型
const QuestionnairePart = sequelize.define("questionnaire_parts", {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    questionnaire_id: {
        type: DataTypes.INTEGER,
        references: {
            model: PsychologyQuestionnaire,
            key: 'id'
        }
    },
    question_id: {
        type: DataTypes.INTEGER,
        references: {
            model: Question,
            key: 'id'
        }
    }
}, {
    timestamps: false
});

// 心理报告模型
const PsychologyReport = sequelize.define("psychology_reports", {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    user_id: {
        type: DataTypes.INTEGER,
        references: {
            model: User,
            key: 'id'
        }
    },
    total_score: {
        type: DataTypes.INTEGER
    },
    content: {
        type: DataTypes.TEXT
    },
    user_answers: {
        type: DataTypes.JSON, // 用于存储用户选择的答案，以 JSON 格式存储
        defaultValue: null
    },
    range_results: {
        type: DataTypes.JSON, // 用于存储各题目段的分析结果
        defaultValue: null
    },
    mental_status: {
        type: DataTypes.STRING(50), // 综合心理状态
        defaultValue: null
    },
    advice: {
        type: DataTypes.TEXT, // 综合建议
        defaultValue: null
    },
    detailed_analysis: {
        type: DataTypes.TEXT, // 详细分析JSON字符串
        defaultValue: null
    },
    questionnaire_id: {
        type: DataTypes.INTEGER,
        references: {
            model: PsychologyQuestionnaire,
            key: 'id'
        }
    }
}, {
    timestamps: false
});

// 题目分数段和心理状态关联模型
const QuestionScoreRange = sequelize.define("question_score_ranges", {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    questionnaire_id: {
        type: DataTypes.INTEGER,
        references: {
            model: PsychologyQuestionnaire,
            key: 'id'
        }
    },
    range_name: {
        type: DataTypes.STRING(50),
        defaultValue: '默认分段'
    },
    start_question_id: {
        type: DataTypes.INTEGER
    },
    end_question_id: {
        type: DataTypes.INTEGER
    },
    score_min: {
        type: DataTypes.INTEGER
    },
    score_max: {
        type: DataTypes.INTEGER
    },
    mental_status: {
        type: DataTypes.STRING(50)
    },
    advice: {
        type: DataTypes.TEXT
    }
}, {
    timestamps: false
});

// 消息通知模型
const Notification = sequelize.define("notifications", {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    user_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: {
            model: User,
            key: 'id'
        }
    },
    sender_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: {
            model: User,
            key: 'id'
        }
    },
    content: {
        type: DataTypes.TEXT,
        allowNull: false
    },
    type: {
        type: DataTypes.STRING(50),
        allowNull: false
    },
    booking_id: {
        type: DataTypes.INTEGER,
        references: {
            model: ConsultationRoom,
            key: 'id'
        }
    },
    is_read: {
        type: DataTypes.BOOLEAN,
        defaultValue: false
    },
    created_at: {
        type: DataTypes.DATE,
        defaultValue: DataTypes.NOW
    }
}, {
    timestamps: false
});

// 预约模型
const Appointment = sequelize.define("appointments", {
    appointment_id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    user_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: {
            model: User,
            key: 'id'
        }
    },
    counselor_or_assistant_id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        references: {
            model: User,
            key: 'id'
        }
    },
    appointments_phone: {
        type: DataTypes.STRING(50)
    },
    room_number: {
        type: DataTypes.STRING(10),
        allowNull: false
    },
    start_time: {
        type: DataTypes.DATE,
        allowNull: false
    },
    end_time: {
        type: DataTypes.DATE,
        allowNull: false
    },
    status: {
        type: DataTypes.STRING(20),
        allowNull: false,
        defaultValue: 'pending'
    }
}, {
    timestamps: false
});

// 定义模型关系

// 咨询室与用户关系
ConsultationRoom.belongsTo(User, {
    foreignKey: 'user_id',
    as: 'user'
});
User.hasMany(ConsultationRoom, {
    foreignKey: 'user_id',
    as: 'consultations'
});

// 心理报告与用户关系
PsychologyReport.belongsTo(User, {
    foreignKey: 'user_id',
    as: 'user'
});
User.hasMany(PsychologyReport, {
    foreignKey: 'user_id',
    as: 'reports'
});

// 问卷部分与问卷关系
QuestionnairePart.belongsTo(PsychologyQuestionnaire, {
    foreignKey: 'questionnaire_id',
    as: 'questionnaire'
});
PsychologyQuestionnaire.hasMany(QuestionnairePart, {
    foreignKey: 'questionnaire_id',
    as: 'parts'
});

// 问卷部分与题目关系
QuestionnairePart.belongsTo(Question, {
    foreignKey: 'question_id',
    as: 'question'
});
Question.hasMany(QuestionnairePart, {
    foreignKey: 'question_id',
    as: 'questionnaireParts'
});

// 题目选项与题目关系
QuestionOption.belongsTo(Question, {
    foreignKey: 'question_id',
    as: 'question'
});
Question.hasMany(QuestionOption, {
    foreignKey: 'question_id',
    as: 'options'
});
// 心理报告与心理问卷关系
PsychologyReport.belongsTo(PsychologyQuestionnaire, {
    foreignKey: 'questionnaire_id',
    as: 'questionnaire'
});
PsychologyQuestionnaire.hasMany(PsychologyReport, {
    foreignKey: 'questionnaire_id',
    as:'reports'
});
// 定义消息通知表与用户表（接收用户）的关系
Notification.belongsTo(User, {
    foreignKey: 'user_id',
    as:'recipientUser'
});
User.hasMany(Notification, {
    foreignKey: 'user_id',
    as:'receivedNotifications'
});

// 定义消息通知表与用户表（发送用户）的关系
Notification.belongsTo(User, {
    foreignKey:'sender_id',
    as:'senderUser'
});
User.hasMany(Notification, {
    foreignKey:'sender_id',
    as:'sentNotifications'
});

// 定义消息通知表与咨询室表的关系
Notification.belongsTo(ConsultationRoom, {
    foreignKey: 'booking_id',
    as: 'booking'
});
ConsultationRoom.hasMany(Notification, {
    foreignKey: 'booking_id',
    as: 'notifications'
});

// 预约与用户关系
Appointment.belongsTo(User, {
    foreignKey: 'user_id',
    as: 'user'
});
User.hasMany(Appointment, {
    foreignKey: 'user_id',
    as: 'appointments'
});

// 预约与咨询师或辅导员关系
Appointment.belongsTo(User, {
    foreignKey: 'counselor_or_assistant_id',
    as: 'counselorOrAssistant'
});
User.hasMany(Appointment, {
    foreignKey: 'counselor_or_assistant_id',
    as: 'receivedAppointments'
});

// 初始化数据库
async function init() {
    try {
        // 临时禁用外键检查，以避免可能的错误
        await sequelize.query('SET FOREIGN_KEY_CHECKS = 0');
        
        console.log('开始同步数据库模型...');
        
        // 按顺序同步模型，先同步基础模型
        await User.sync({ alter: true });
        console.log('User模型同步完成');
        
        await PsychologyKnowledge.sync({ alter: true });
        console.log('PsychologyKnowledge模型同步完成');
        
        await PsychologyQuestionnaire.sync({ alter: true });
        console.log('PsychologyQuestionnaire模型同步完成');
        
        await Question.sync({ alter: true });
        console.log('Question模型同步完成');
        
        // 同步依赖关系模型
        await QuestionOption.sync({ alter: true });
        console.log('QuestionOption模型同步完成');
        
        await QuestionnairePart.sync({ alter: true });
        console.log('QuestionnairePart模型同步完成');
        
        await ConsultationRoom.sync({ alter: true });
        console.log('ConsultationRoom模型同步完成');
        
        await QuestionScoreRange.sync({ alter: true });
        console.log('QuestionScoreRange模型同步完成');
        
        await PsychologyReport.sync({ alter: true });
        console.log('PsychologyReport模型同步完成');
        
        await Notification.sync({ alter: true });
        console.log('Notification模型同步完成');
        
        await Appointment.sync({ alter: true });
        console.log('Appointment模型同步完成');
        
        // 重新启用外键检查
        await sequelize.query('SET FOREIGN_KEY_CHECKS = 1');
        
        console.log('所有数据库模型同步完成');
    } catch (error) {
        console.error('数据库同步过程中出错:', error);
        // 确保无论发生什么错误，都重新启用外键检查
        try {
            await sequelize.query('SET FOREIGN_KEY_CHECKS = 1');
        } catch (e) {
            console.error('重新启用外键检查时出错:', e);
        }
        throw error;
    }
}

// 导出初始化方法和模型
module.exports = {
    init,
    User,
    PsychologyKnowledge,
    ConsultationRoom,
    PsychologyQuestionnaire,
    Question,
    QuestionOption,
    QuestionnairePart,
    PsychologyReport,
    Notification,
    QuestionScoreRange,
    Appointment
};    